package myclass;
import java.util.Arrays;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class peoplecheck {
	public int add(String hubie,String fangleixing,String mianji,String fangjianshu,String huzhu,String sfz,String sex,String mingzu,String jiaoyu) throws SQLException, ClassNotFoundException
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		
		String sql="insert into people(hubie,fangleixing,mianji,fangjianshu,huzhu,sfz,sex,mingzu,jiaoyu) values(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, hubie);
		ptmt.setString(2, fangleixing);
		ptmt.setString(3, mianji);
		ptmt.setString(4, fangjianshu);
		ptmt.setString(5, huzhu);
		ptmt.setString(6, sfz);
		ptmt.setString(7, sex);
		ptmt.setString(8, mingzu);
		ptmt.setString(9, jiaoyu);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	public void shanchu(String huzhu) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="delete from people where huzhu = ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, huzhu);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
	}
	public String browse()throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String str=null;
		String sql="select * from people";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ResultSet rs =ptmt.executeQuery();
		int i=0;
		while(rs.next())
		{
			int id=rs.getInt(1);
			String cname=rs.getString(6);
			String tname=rs.getString(8);
			String cwhere=rs.getString(9);
			String zuihou=rs.getString(10);
			if(i==0)
			{
				str=cname+"  "+tname+"  "+cwhere+"  "+zuihou+".";
				i=1;
			}
			else
			{
				str=str+cname+"  "+tname+"  "+cwhere+"  "+zuihou+".";
			}
		}
		ptmt.close();
		conn.close();
		return str;
	}
	public String chaxun(String huzhu,String sex,String mingzu,String jiaoyu) throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String hh="小明";
		String sql="select * from people where huzhu = ? ";
		if(huzhu!=null&&sex==null&&mingzu==null&&jiaoyu==null)
		{
			hh=huzhu;
			sql="select * from people where huzhu = ? ";
		}
		else if(sex!=null&&mingzu==null&&jiaoyu==null&&huzhu==null)
		{
			hh=sex;
			sql="select * from people where sex = ? ";
		}
		else if(mingzu!=null&&jiaoyu==null&&huzhu==null&&sex==null)
		{
			hh=mingzu;
			sql="select * from people where mingzu = ? ";
		}
		else if(jiaoyu!=null&&sex==null&&mingzu==null&&huzhu==null)
		{
			hh=jiaoyu;
			sql="select * from people where jiaoyu = ? ";
		}
		String str = null;
		//String sql="select * from people where huzhu = ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,hh);
		ResultSet rs =ptmt.executeQuery();
		while(rs.next())
		{
			int id=rs.getInt(1);
			String cname=rs.getString(6);
			String tname=rs.getString(8);
			String cwhere=rs.getString(9);
			String zuihou=rs.getString(10);
			
			str=cname+" "+tname+" "+cwhere+" "+zuihou+".";
		}
		ptmt.close();
		conn.close();
		return str;
	}
	public int xiugai(String huzhu,String sfz,String sex,String mingzu,String jiaoyu) throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/teacher_system";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		
		String sql="update people "
				+ "set sfz=?,"
				+ "sex=?,"
				+ "mingzu=?,"
				+ "jiaoyu=? "
				+ "where huzhu= ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		
			ptmt.setString(1, sfz);
		
	
		
			ptmt.setString(2, sex);
		
		
		
			ptmt.setString(3, mingzu);
		
		
		
			ptmt.setString(4, jiaoyu);
		
		ptmt.setString(5, huzhu);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
}
